[dbt] 生成するデータモデルの種類を変える「Materializations」を試す

大阪オフィスの玉井です。

dbtは、データ変換を行い、接続先のDWHにデータモデルを生成します。ただ、その「データモデル」ってそもそも何？と思う方がほとんどだと思います。

dbtは、変換した「データモデル」を色々な形でDWHに生成することができます。これをMaterializationsと呼びます。今回はdbtで設定できるMaterializationsのうち、基本となる3つをご紹介します。

基本のMaterializations3つの概要

今回紹介するMaterializationsの超概要について、先に紹介します。

• table
  • DWHに、データモデルを「テーブル」として作成します。
• view
  • DWHに、データモデルを「ビュー」として作成します。
• ephemeral
  • DWHに、何らかの作成を行いません。
  • データモデルのクエリにおける、共通テーブル式（CTE）として扱います。

おそらく、実際に実行してみた方が理解が進むと思います。

検証環境

• macOS Catalina 10.15.7
• dbt CLI 0.18.1
• Google BigQuery

やってみた

今回は、検証用として、下記のデータモデルを使います。

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle','raw_order') }}

table

上記のデータモデルを「table」として生成したいと思います。このデータモデルのsqlファイルに、Materializationsの指定を追記します。

{{ config(
    materialized='table'
)}}

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle','raw_order') }}

configの部分で、このデータモデルのMaterializationsをtableに指定しました。

このデータモデルを実行します。CREATE TABLEと表示されましたね。

$ dbt run --models stg_orders
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources

12:22:54 | Concurrency: 4 threads (target='learn')
12:22:54 |
12:22:54 | 1 of 1 START table model jaffle.stg_orders........................... [RUN]
12:22:58 | 1 of 1 OK created table model jaffle.stg_orders...................... [CREATE TABLE (99.0 rows, 3.3 KB processed) in 4.26s]
12:22:58 |
12:22:58 | Finished running 1 table model in 8.97s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

DWH側に生成されたデータモデルを確認します。確かにテーブルとして作られていますね。

view

先程「テーブル」として生成したデータモデルを、今度は「ビュー」として作り直してみます。

データモデルを下記のように書き換えます。

{{ config(
    materialized='view'
)}}

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle','raw_order') }}

設定部分をviewに変更しただけです。

このデータモデルを実行します。今度はCREATE VIEWと表示されましたね。ちなみに--full-refreshというオプションをつけている理由ですが、先程の検証で、このデータモデルは既にテーブルとして存在しているため、そのまま実行してもエラーになります。同データモデルを「完全に作り直す」ために、このオプションを使用しています。

$ dbt run --models stg_orders --full-refresh
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources

12:28:16 | Concurrency: 4 threads (target='learn')
12:28:16 |
12:28:16 | 1 of 1 START view model jaffle.stg_orders............................ [RUN]
12:28:21 | 1 of 1 OK created view model jaffle.stg_orders....................... [CREATE VIEW in 4.99s]
12:28:21 |
12:28:21 | Finished running 1 view model in 7.39s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

DWH側に生成されたデータモデルを確認します。ビューとして生成されています。

ephemeral

テーブルやビューと違って、あまりしっくりこないephemeralを試してみます。記事の冒頭でも述べましたが、このMaterializationはDWHに何らかの形でデータモデルを残すことは行いません（何も生成されない）。

まず、事前に、先程生成したビューを（DWH側で）削除しておきます。

データモデルを下記のように書き換えます。

{{ config(
    materialized='ephemeral'
)}}

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle','raw_order') }}

このデータモデルを実行します。何もCREATEしてない様子。

$ dbt run --models stg_orders --full-refresh
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources

12:34:03 | Concurrency: 4 threads (target='learn')
12:34:03 |
12:34:03 |
12:34:03 | Finished running  in 1.76s.

DWHを確認します。何も生成されていません。

ephemeralなデータモデルを参照しているデータモデルの検証

ephemeralが指定されているデータモデルは、上記の検証の通り、DWHに何も生成しません。しかし、dbt上では、これもまた「データモデル」の一つとして定義されているわけです。

そこで、ephemeralのデータモデルを参照している別のデータモデルはどのようになるのか、実際に見てみたいと思います。

さっきのephemeralにしたデータモデルを参照するデータモデルを用意します。stg_ordersとstg_paymentsのMaterializationsはephemeralになっています。このデータモデル自体（fct_orders）はテーブルとして生成します。

{{ config(
    materialized='table'
)}}

with orders as  (
    select * from {{ ref('stg_orders' )}}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

order_payments as (
    select
        order_id,
        sum(case when status = 'success' then amount end) as amount

    from payments
    group by 1
),

final as (

    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        coalesce(order_payments.amount, 0) as amount

    from orders
    left join order_payments using (order_id)
)

select * from final

このデータモデルを実行します。テーブルが1つだけ作成されました。

$ dbt run --models fct_orders --full-refresh
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources

12:44:50 | Concurrency: 4 threads (target='learn')
12:44:50 |
12:44:50 | 1 of 1 START table model jaffle.fct_orders........................... [RUN]
12:44:56 | 1 of 1 OK created table model jaffle.fct_orders...................... [CREATE TABLE (99.0 rows, 5.2 KB processed) in 5.59s]
12:44:56 |
12:44:56 | Finished running 1 table model in 8.36s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

DWH側を確認します。fct_ordersはテーブルとして生成されていますが、このテーブルの参照元となっているstg_ordersとstg_paymentsはありません。

では、fct_ordersはどうやって生成されたのでしょうか。実際に実行されたクエリはどのようなものなのでしょうか。下記のようになっていました。

 create or replace table `tamai-rei`.`jaffle`.`fct_orders`


  OPTIONS()
  as (
    with  __dbt__CTE__stg_orders as (


select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from `tamai-rei`.`jaffle`.`raw_order`
),  __dbt__CTE__stg_payments as (


select
    id as payment_id,
    orderid as order_id,
    paymentmethod as payment_method,
    status,

    amount / 100 as amount,
    created as created_at

from dbt-tutorial.stripe.payment
),orders as  (
    select * from __dbt__CTE__stg_orders
),

payments as (
    select * from __dbt__CTE__stg_payments
),

order_payments as (
    select
        order_id,
        sum(case when status = 'success' then amount end) as amount

    from payments
    group by 1
),

final as (

    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        coalesce(order_payments.amount, 0) as amount

    from orders
    left join order_payments using (order_id)
)

select * from final
  );

参照元のephemeralのデータモデルの部分が、WITH句として挿入されています。記事の冒頭に書いた「データモデルのクエリにおける、共通テーブル式（CTE）として扱います。」というのは、こういう意味でした。それ自体がデータモデルとして生成されることはないですが、他のデータモデルからの参照としては、キッチリと認識されるということです。

検討事項（使い分け）

例えば、BIツールから頻繁に参照するようなデータモデルの場合は、パフォーマンスの観点から、実テーブルとして生成した方が良いと思いました。逆に、そこまで参照におけるパフォーマンスを重視しない場合は、ビューにしておいた方が、DWH側の容量の削減になるので、良いと思いました。

では、「ephemeral」はどういったときに使えばいいのでしょうか？

データ変換において「それ自体は必要ないけど、中間テーブルとして必要」というモデルは、結構頻繁に登場します。そういうデータモデルはephemeralにすれば良いと思います。DWH側にも、余分なテーブルやビューが増えなくなります。しかし、ephemeralは、参照先のデータモデルに、共通テーブル式として挿入される以上、実際に実行されるクエリが複雑になることに注意する必要があります（後で見直す時に、何をやっているか理解しづらくなる）。

Materializationsの指定方法

Materializationsを指定するにあたり、今までは、それぞれモデルファイルに、いちいちconfigという記述を書いてきましたが、dbt_project.ymlで一括指定することができます。

...
models:
  jaffle_shop:
      staging:
          materialized: view
      marts:
          materialized: table

上記のように書くと、例えば、modelsディレクトリ内のstagingディレクトリ下にあるデータモデルは全てビューとして生成されます。この場合、いちいち各モデルファイルにconfigを指定する必要はありません（指定した場合、config側の設定が優先される）。

おわりに

公式ドキュメントはこちら。

上記の見ればわかりますが、Materializationsには、もう一つincrementalというのがあります。ただ、こちらは少々複雑なため、別記事にして、ご紹介したいと思います。